# @Author: 唐奇才
# @Time: 2021/5/5 16:43
# @File: saveReliesToMysql.py
# @Software: PyCharm
import time

import pymysql
from getCommentData import getJsonData, oid


def isExistTable(tableName):
    print("检查表")
    isExist = False
    try:
        conn = pymysql.connect(host="localhost", port=3306, user="root", password="1234", db="bilibili", )
        print("检查表处连接服务器成功!")
        # 2.获取游标对象
        cursor = conn.cursor()
        sql = """
                show tables
            """
        cursor.execute(sql)
        tables = cursor.fetchall()

        isExist = (tableName,) in tables

    except Exception as e:
        print("连接服务器失败.....")
        print(type(e), e)
    finally:
        if cursor:
            cursor.close()

        if conn:
            conn.close()

        return isExist


def createTable(tableName):
    print("创建表")
    try:
        conn = pymysql.connect(host="localhost", port=3306, user="root", password="1234", db="bilibili", )
        print("创建表处连接服务器成功!")
        # 2.获取游标对象
        cursor = conn.cursor()
        sql = """
                CREATE TABLE `{}`  (
                  `rpid` varchar(255) NOT NULL,
                  `uname` varchar(255) NULL,
                  `sex` varchar(255) NULL,
                  `like` integer(255) NULL,
                  `current_level` int(255) NULL,
                  `lmessage` text NULL
                );
            """.format(str(tableName))
        cursor.execute(sql)
        conn.commit()

    except Exception as e:
        print("连接服务器失败.....")
        print(type(e), e)
    finally:
        if cursor:
            cursor.close()

        if conn:
            conn.close()


def saveDataToMysql1(tableName, data):
    print("保存表")
    if not isExistTable(tableName):
        createTable(tableName)
    try:
        conn = pymysql.connect(host="localhost", port=3306, user="root", password="1234", db="bilibili", )
        print("保存出连接服务器成功!")
        # 2.获取游标对象
        cursor = conn.cursor()
        count = 0
        for d in data:
            sql = """
                     INSERT INTO `{}`(`rpid`, `uname`, `sex`, `like`, `current_level`, `lmessage`) 
                     VALUES ('{}', '{}' ,'{}', {}, {}, '{}')
                """.format(tableName, str(d[0]), str(d[1]), d[2], d[3], str(d[4]), str(d[5]))
            cursor.execute(sql)
            conn.commit()
            count += 1
        print("Successfully save")

    except Exception as e:
        print("连接服务器失败.....")
        print("在第{}个出现失败现象")
        print(type(e), e)
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


def countContentNum(tableName):
    print("查数表")
    try:
        conn = pymysql.connect(host="localhost", port=3306, user="root", password="1234", db="bilibili", )
        print("保存出连接服务器成功!")
        # 2.获取游标对象
        cursor = conn.cursor()
        count = 0

        sql = """
            select count(*) from `{}`        
        """.format(tableName)
        cursor.execute(sql)
        conn.commit()
        result = cursor.fetchall()
        return result[0][0]

    except Exception as e:
        print("连接服务器失败.....")
        print("在第{}个出现失败现象")
        print(type(e), e)
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


def saveDataToMysql(tableName, data):
    print("保存表")
    if not isExistTable(tableName):
        createTable(tableName)
    while data:
        try:
            conn = pymysql.connect(host="localhost", port=3306, user="root", password="1234", db="bilibili", )
            print("保存处连接服务器成功!", end="|")
            # 2.获取游标对象
            cursor = conn.cursor()
            count = 1
            d = data.pop()
            sql = """
                         INSERT INTO `{}`(`rpid`, `uname`, `sex`, `like`, `current_level`, `lmessage`) 
                         VALUES ('{}', '{}' ,'{}', {}, {}, '{}')
                    """.format(tableName, str(d[0]), str(d[1]), d[2], d[3], str(d[4]), str(d[5]))
            cursor.execute(sql)
            conn.commit()
            count += 1
            print("Successfully save", end="|")

        except Exception as e:
            print("连接服务器失败.....")
            print("在第{}个出现失败现象".format(count))
            print(type(e), e)
        finally:
            if cursor:
                cursor.close()
            if conn:
                conn.close()
    print("\n爬取完成!^_^!")


from testSpider import getHTML, getJsonDataFrom

baseURL = "https://api.bilibili.com/x/v2/reply/main?" \
          "callback={}" \
          "&oid={}" \
          "&type=1" \
          "&next={}"


def getJsonData(cb, oid):
    info = []
    page = 1
    pause = -2
    count = 1
    while True:
        try:
            data = getHTML(baseURL.format(str(cb), str(oid), str(page)))
            jdata = getJsonDataFrom(data)
            print("数据获取成功")
            isEnd = jdata["data"]["cursor"]["is_end"]

            if page == pause:
                print("暂停~~~~~~~~~~")
                break
            if not isEnd:
                page += 1
                jdata = jdata["data"]["replies"]

                oneData = []
                for jd in jdata:
                    oneData.append(jd["rpid"])
                    oneData.append(jd["member"]["uname"])
                    oneData.append(jd["member"]["sex"])
                    oneData.append(jd["like"])
                    oneData.append(jd["member"]["level_info"]["current_level"])
                    oneData.append(jd["content"]["message"].replace(" ", "").replace("\n", ""))

                    info.append(oneData)
                    oneData = []
                # print(info)
                saveDataToMysql(str(oid), info)
                info = []
                print("休息一下。。。。。。")
                time.sleep(1)

            else:
                print("数据爬取完毕")
                break

        except Exception as e:
            print(e)
            print("可能被服务器拒绝了！第{}次尝试".format(count))


def getOidFromMysql():
    try:
        conn = pymysql.connect(host="localhost", port=3306, user="root", password="1234", db="bilibili", )
        print("连接服务器成功!")
        # 2.获取游标对象
        cursor = conn.cursor()
        # 3.执行sql语句
        sql = """
            select oid from hotcontent
        """
        # 返回结果的数量
        count = cursor.execute(sql)
        print("结果数量: %s" % count)
        # 获取所有内容
        myresult = cursor.fetchall()

    except Exception as e:
        print("连接服务器失败.....")
        print(type(e), e)
    finally:
        if cursor:
            cursor.close()
            print("关闭游标")
        if conn:
            conn.close()
            print("关闭链接")
        return myresult


def main():
    # oid = 672756810
    cb = "jQuery1720717671480774899_1620107047887"
    # getJsonData(cb, oid)
    oids = getOidFromMysql()
    oids = list(oids)[:-19]
    count = 1
    while oids:
        oid = oids.pop()[0]
        print("开始爬取第{}跳数据，oid为：{}".format(count, oid))
        getJsonData(cb, oid)
        count += 1
        time.sleep(10)

    print("全部爬取完成")


# oids = getOidFromMysql()
# print(oids[0][0])


def test():
    oids = getOidFromMysql()
    print(oids[0][0])
    check = [countContentNum(oid[0]) for oid in oids]
    print(check, len(check), sum(check))


if __name__ == '__main__':
    # main()
    test()
